﻿'Description:流水卡领料、工艺确认类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2017-9-4
'Rewriter:
'Rewrite Date:
Imports DatabaseAccess.DBUtility
Imports System.Configuration
Imports BusinessRule.DTO
Imports BusinessRule.IBusiness
Imports System.Data.OracleClient
Imports System.Text
Public Class uLMSContainerMaterialBusiness
    Implements uLMSContainerMaterialIBusiness
    Private m_CommonObj As uLMSCommonBusiness = New uLMSCommonBusiness

    ''' <summary>
    ''' 获取领料、工艺确认等开卡后的批次
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <param name="PageIndex"></param>
    ''' <param name="PageSize"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMainTableNew(ByVal dto As MaterialParamterDto, ByVal PageIndex As Integer, ByVal PageSize As Integer) As uMESProcessResult Implements uLMSContainerMaterialIBusiness.GetMainTableNew
        Dim strContainerName As String = ""
        Dim strTogetherFlag As String = ""
        Dim strProductName As String = ""
        If dto.strContainerName.Trim() <> "" Then
            strContainerName = dto.strContainerName.Trim()
            Dim r1 As uMESProcessResult = GetContainerInfo(strContainerName)
            If r1.Result = True Then
                Try
                    strTogetherFlag = r1.DBTable.Rows(0)("TogetherFlag").ToString
                    strProductName = r1.DBTable.Rows(0)("productname").ToString
                Catch ex As Exception
                    strTogetherFlag = ""
                End Try
            End If
        End If

        Dim strWorkFlowName As String = ConfigurationSettings.AppSettings("DefaultMetalFlowName") '流程名称

        Dim strSql As String = "SELECT ws.workflowstepname,sb.specname,s.roleid,ws.workflowid,wb.workflowname,ws.sequence,r.rolename  FROM workflowstep ws " & vbCrLf & _
                               "LEFT JOIN workflow w ON w.workflowid=ws.workflowid " & vbCrLf & _
                               "LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid " & vbCrLf & _
                               "LEFT JOIN specbase sb ON sb.specbaseid =ws.specbaseid " & vbCrLf & _
                               "LEFT JOIN spec s ON s.specid =sb.revofrcdid " & vbCrLf & _
                               "LEFT JOIN roledef r ON r.roleid=s.roleid " & vbCrLf & _
                               "WHERE wb.workflowname='" & strWorkFlowName & "' ORDER BY ws.sequence"

        Dim workFlowTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim strNextRoleID As String = ""
        Dim strRoleName As String = ""
        Dim strRoleID As String = ""
        'modify tianFW 2017/10/25 添加 WB.WORKFLOWNAME ||':'|| WF.WORKFLOWREVISION  WORKFLOWINFO的查询 
        'modify tianFW 2017/10/25 添加需要条件("LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID = W.WORKFLOWBASEID ")
        Dim sqlSelect As String = "SELECT Distinct DECODE(c.containertype,7,NULL,c.containerid) facontainerid,c.containertype,DECODE(c.containertype,7,NULL,c.containername) containername,c.TogetherFlag,DECODE(c.containertype,7,NULL,c.containerid) containerid,c.mfgworkflowid,pb.productname,p.productrevision,c.productid,p.description,f.factoryname,sb.specname," & vbCrLf & _
                            "s.specrevision,ws.sequence, ws.workflowstepname,ws.islaststep,cts.specid,cts.workflowstepid,cl.containerlevelname," & vbCrLf & _
                            "c.qty,c.factorystartqty,to_date(DECODE(c.containertype,7,NULL,cts.lastmovedate)) lastmovedate,c.isselfmfg,c.materialrequirementdate,c.plannedstartdate, s.roleid,to_date(DECODE(c.containertype,7,NULL,c.factorystartdate)) factorystartdate, " & vbCrLf & _
                            "(CASE WHEN c.holdreasonid IS NOT NULL THEN 1 ELSE 0 END) AS IsHold,ut3.attributevalue as CheckTechnique,'' AS BeginStep,m.mfgordername, " & vbCrLf & _
                            " WB.WORKFLOWNAME ||':'|| WF.WORKFLOWREVISION  WORKFLOWINFO,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch "
        If dto.IsSubWorkflow = True Then '查询子工艺 add:Wangjh 20171215
            sqlSelect += ",subwb.workflowname||':'||subw.workflowrevision subWorkflowname,subw.workflowid subworkflowid,subw.factoryid subFactoryID,DECODE(c.containertype,7,NULL,swc.Subworkflowcheckinfoid) Subworkflowcheckinfoid"
        ElseIf dto.IsSubWorkflow = False Then
            sqlSelect += " ,null subWorkflowname,null subworkflowid,null subFactoryID,null Subworkflowcheckinfoid"
        End If
        Dim sql As String = sqlSelect &
                            " FROM container c " & vbCrLf & _
                            " left join mfgorder m on m.mfgorderid=c.mfgorderid" & vbCrLf & _
                            "LEFT JOIN containerlevel cl ON cl.containerlevelid = c.levelid " & vbCrLf & _
                            "LEFT JOIN product p ON p.productid = c.productid " & vbCrLf & _
                            "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid " & vbCrLf & _
                            "LEFT JOIN currentstatus cts ON cts.currentstatusid = c.currentstatusid " & vbCrLf & _
                            "LEFT JOIN spec s ON s.specid = cts.specid " & vbCrLf & _
                            "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid " & vbCrLf & _
                            "LEFT JOIN workflowstep ws ON ws.workflowstepid = cts.workflowstepid " & vbCrLf & _
                            "LEFT JOIN workflow w on w.workflowid=ws.workflowid " & vbCrLf & _
                            "LEFT JOIN WORKFLOW WF ON WF.WORKFLOWID = C.MFGWORKFLOWID " & vbCrLf & _
                            "LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID = WF.WORKFLOWBASEID " & vbCrLf & _
                            "LEFT JOIN cdodefinition cdo on cdo.cdodefid=w.cdotypeid " & vbCrLf & _
                            "LEFT JOIN factory f ON f.factoryid = cts.factoryid " & vbCrLf & _
                            "LEFT JOIN userattribute ut2 ON ut2.parentid=c.containerid AND ut2.userattributename='BeginStep' " & vbCrLf & _
                            "LEFT JOIN userattribute ut3 ON ut3.parentid=c.containerid AND ut3.userattributename='CheckTechnique' " &
                            " LEFT JOIN CONTAINERCURRENTINFO CCI ON CCI.CONTAINERID=C.CONTAINERID"
        If dto.IsSubWorkflow = True Then '查询子工艺 add:Wangjh 20171215
            sql += " LEFT JOIN SubWorkflowCheckInfo swc ON swc.containerid=c.containerid" & vbCrLf & _
                    "LEFT JOIN employee sube ON sube.employeeid=swc.checktechniqueid" & vbCrLf & _
                    "LEFT JOIN Workflow subw ON subw.workflowid=swc.subworkflowid" & vbCrLf & _
                    "LEFT JOIN workflowbase subwb ON subwb.workflowbaseid=subw.workflowbaseid" & vbCrLf & _
                    "LEFT JOIN factory subfa ON subfa.factoryid=subw.factoryid"
        End If
        Dim strCondition As String = " WHERE c.status = 1 AND c.containername <> c.containerid AND c.parentcontainerid IS NULL " & vbCrLf & _
                                     "AND ws.workflowstepname<>'完工入库' and cdo.cdoname='Workflow' "
        If dto.IsSubWorkflow = False Then '主工艺确认才用
            strCondition += "  and (CASE WHEN ws.workflowstepname='工艺确认' THEN cci.techniquedate ELSE NULL END) IS NULL  AND s.roleid IN ( " & vbCrLf & _
                            "SELECT em.roleid FROM employeerole em LEFT JOIN employee e ON e.employeeid = em.employeeid WHERE LOWER(e.employeename) = '" & dto.strEmployeeName.ToLower() & "'" & vbCrLf & _
                            " ) "
        End If

        If dto.IsSubWorkflow = True Then '查询子工艺 add:Wangjh 20171215
            strCondition += " and swc.checkresult=0 and  (CASE WHEN INSTR( f.factoryname,'钣焊')>0 THEN swc.planemployeeid ELSE '1' END) IS NOT NULL"
        End If
        sql += strCondition
        If Not String.IsNullOrWhiteSpace(dto.StrMaterialStep.Trim) Then
            If dto.IsSubWorkflow = False Then '主工艺校对才用
                If dto.IsMaterialStep Then
                    sql += " AND WS.WORKFLOWSTEPNAME='" + dto.StrMaterialStep + "'"
                Else
                    sql += " AND WS.WORKFLOWSTEPNAME<>'" + dto.StrMaterialStep + "'"
                End If
            End If
        End If

        If dto.IsEmp = True Then
            If dto.IsSubWorkflow = True Then '查询子工艺 add:Wangjh 20171215
                sql += " and sube.employeename='" & dto.strEmployeeName & "' "
            ElseIf dto.IsSubWorkflow = False Then
                sql += " AND ut3.attributevalue= '" & dto.strEmployeeName & "' "
            End If
        End If

        If dto.WorkCenterID IsNot Nothing Then 'add tianFW 2018年1月9日 计划员属于工区
            sql += " AND C.WORKCENTERID= '" & dto.WorkCenterID & "' "
        End If

        If strTogetherFlag.Trim() <> "" Then
            sql += "AND c.TogetherFlag= '" & strTogetherFlag & "'" & vbCrLf
            sql += String.Format(" AND LOWER(pb.productname)=LOWER('{0}') ", strProductName)
        Else
            If dto.strContainerName.Trim() <> "" Then
                sql += String.Format(" AND LOWER(c.containername) like LOWER('%{0}%') ", strContainerName)
            End If
        End If

        ''产品图号
        If dto.ProductName.Trim <> "" Then
            sql += String.Format(" AND LOWER(pb.productname) like LOWER('%{0}%') ", dto.ProductName.Trim)
        End If

        ''车间，如果有“车间”两个字则过滤，否则不过滤
        Dim strFactoryName As String = dto.FactoryName
        If strFactoryName <> "" Then  'update:Wangjh 2017-6-28 去除 And strFactoryName.Contains("车间") 
            If dto.IsSubWorkflow = True Then '查询子工艺 add:Wangjh 20171215
                sql += "AND subfa.factoryname = '" & dto.FactoryName & "'" & vbCrLf
            ElseIf dto.IsSubWorkflow = False Then
                sql += "AND f.factoryname = '" & dto.FactoryName & "'" & vbCrLf
            End If
        End If

        If dto.strStartDate.Trim() <> "" Then
            sql += "AND c.plannedstartdate >= TO_DATE('" & dto.strStartDate & " 00:00:00','yyyy-mm-dd hh24:mi:ss')" & vbCrLf
        End If

        If dto.strEndDate.Trim() <> "" Then
            sql += "AND c.plannedstartdate <= TO_DATE('" & dto.strEndDate & " 23:59:59','yyyy-mm-dd hh24:mi:ss')" & vbCrLf
        End If
        If Not String.IsNullOrWhiteSpace(dto.MfgOrderName) Then
            sql += "AND m.mfgordername ='" & dto.MfgOrderName & "'" & vbCrLf
        End If
        sql += "ORDER BY c.togetherflag" & vbCrLf

        Dim para As uMESPagingDataDTO = New uMESPagingDataDTO()
        para.CurrentPageIndex = PageIndex
        para.PageSize = PageSize
        para.strSQL = sql
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(para)
        Dim tmpTable As DataTable = result.DBTable.Copy()

        Dim strContianerIDList As String = ""
        For Each row As DataRow In tmpTable.Rows
            strContianerIDList = strContianerIDList + "'" + row("containerid").ToString().Trim() + "',"
        Next
        If strContianerIDList.Trim() <> "" Then
            strContianerIDList = strContianerIDList.Substring(0, strContianerIDList.Length - 1)
        Else
            Return result
        End If

        sql = "SELECT c.containername,t.attributevalue,t.userattributename,t.parentid FROM Userattribute t LEFT JOIN container c ON c.containerid=t.parentid WHERE t.parentid IN (" & strContianerIDList & ")"

        'sql = "SELECT d.dataname,d.datavalue,c.containername,h.txndate  FROM Datapointhistory t " & vbCrLf & _
        '      "LEFT JOIN Datapointhistorydetail d ON d.datapointhistoryid=t.datapointhistoryid " & vbCrLf & _
        '      "LEFT JOIN historymainline h ON h.historymainlineid=t.historymainlineid " & vbCrLf & _
        '      "LEFT JOIN container c ON c.containerid=h.containerid " & vbCrLf & _
        '      "WHERE h.reversalstatus=1 AND  c.containerid IN (" & strContianerIDList & ")  ORDER BY h.txndate DESC"

        Dim dataPointTable As DataTable = DatabaseHelper.Query_ReturnDatTable(sql)

        tmpTable.Columns.Add("实发数/顺序号")
        tmpTable.Columns.Add("熔炉号子")
        tmpTable.Columns.Add("熔炉号母")
        tmpTable.Columns.Add("检验编号")
        tmpTable.Columns.Add("代料牌号")
        tmpTable.Columns.Add("代料规格")
        tmpTable.Columns.Add("代料单号")
        tmpTable.Columns.Add("平行作业工序")
        tmpTable.Columns.Add("平行作业单号")
        tmpTable.Columns.Add("CloseContianer") 'add tianFW 2017/09/08
        tmpTable.Columns.Add("canMove")

        For Each row As DataRow In tmpTable.Rows
            AddMaterialInfo(dataPointTable, row)
        Next
        '子表 单件单卡 add:Wangjh 2017-9-1 改为父子表
        Dim dv As DataView = tmpTable.DefaultView
        dv.RowFilter = "containertype='7'"

        Dim containerflag As String = ""
        For Each drv As DataRowView In dv
            containerflag += String.Format(" (c.togetherflag='{0}' AND c.PRODUCTID='{1}' AND WS.WORKFLOWSTEPID='{2}'",
                                            drv("TOGETHERFLAG"), drv("PRODUCTID"), drv("WORKFLOWSTEPID"))
            If IsDBNull(drv("MFGORDERNAME")) Then
                containerflag += String.Format(" AND M.MFGORDERNAME IS NULL) OR")
            Else
                containerflag += String.Format(" AND M.MFGORDERNAME='{0}') OR", drv("MFGORDERNAME"))
            End If
        Next
        If containerflag.Length > 0 Then
            containerflag = containerflag.Substring(0, containerflag.Length - 2)
        End If

        Dim sonSelect As String = " SELECT c.containername,c.containerid,c.productid,c.mfgworkflowid,c.containertype,cu.workflowstepid,c.togetherflag,cu.lastmovedate,c.factorystartdate,c.isselfmfg,ut2.attributevalue BeginStep,m.mfgordername"

        If dto.IsSubWorkflow = True Then '查询子工艺 add:Wangjh 20171215 
            sonSelect += " ,subwb.workflowname||':'||subw.workflowrevision subWorkflowname,subw.workflowid subworkflowid,subw.factoryid subFactoryID,swc.Subworkflowcheckinfoid"
        ElseIf dto.IsSubWorkflow = False Then
            sonSelect += " ,null subWorkflowname,null subworkflowid,null subFactoryID,null Subworkflowcheckinfoid"
        End If
        Dim sonSql As String = sonSelect & " FROM container c " &
                                " LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid" &
                                " LEFT JOIN Currentstatus cu ON cu.currentstatusid=c.currentstatusid" &
                                " LEFT JOIN workflowstep ws ON ws.workflowstepid=cu.workflowstepid" &
                                "  LEFT JOIN workflow w ON w.workflowid=ws.workflowid" &
                                " LEFT JOIN cdodefinition cdo ON cdo.cdodefid=w.cdotypeid" &
                                "  LEFT JOIN userattribute ut2 ON ut2.parentid=c.containerid AND ut2.userattributename='BeginStep' " &
                                "  LEFT JOIN userattribute ut ON ut.parentid=c.containerid AND ut.userattributename='CloseContianer' "
        If dto.IsSubWorkflow = True Then '查询子工艺 add:Wangjh 20171215
            sonSql += " inner JOIN SubWorkflowCheckInfo swc ON swc.containerid=c.containerid" & vbCrLf & _
                   "LEFT JOIN employee sube ON sube.employeeid=swc.checktechniqueid" & vbCrLf & _
                   "LEFT JOIN Workflow subw ON subw.workflowid=swc.subworkflowid" & vbCrLf & _
                   "LEFT JOIN workflowbase subwb ON subwb.workflowbaseid=subw.workflowbaseid" & vbCrLf & _
                   "LEFT JOIN factory subfa ON subfa.factoryid=subw.factoryid"
        Else
            sonSql += " LEFT JOIN CONTAINERCURRENTINFO CCI ON CCI.CONTAINERID=C.CONTAINERID"
        End If
        Dim sonCondition As String = " where c.containertype=7 AND cdo.cdoname='Workflow' AND C.STATUS=1"
        If dto.IsSubWorkflow = True Then '查询子工艺 add:Wangjh 20171215
            sonCondition += " and swc.checkresult=0 and  (CASE WHEN INSTR( subfa.factoryname,'钣焊')>0 THEN swc.planemployeeid ELSE '1' END) IS NOT NULL"
        Else
            sonCondition += " and (CASE WHEN ws.workflowstepname='工艺确认' THEN cci.techniquedate ELSE NULL END) IS NULL "
        End If
        If dto.IsEmp = True Then '查询子工艺 add:Wangjh 20171215
            If dto.IsSubWorkflow = True Then
                sonCondition += " and sube.employeename='" & dto.strEmployeeName & "' "
            End If
        End If

        If strFactoryName <> "" Then '查询子工艺 add:Wangjh 20171215
            If dto.IsSubWorkflow = True Then
                sonCondition += "AND subfa.factoryname = '" & dto.FactoryName & "'" & vbCrLf
            End If
        End If

        If dto.WorkCenterID IsNot Nothing Then 'add tianFW 2018年1月9日 计划员属于工区
            sonCondition += " AND C.WORKCENTERID= '" & dto.WorkCenterID & "' "
        End If

        sonSql += String.Format(sonCondition & " AND  ({0})", containerflag)
        sonSql += " ORDER BY c.containername"
        Dim sonContainerDt As New DataTable
        If containerflag.Length > 0 Then '存在单件单卡的时候才查询子表
            sonContainerDt = DatabaseHelper.Query_ReturnDatTable(sonSql)
        Else
            sonContainerDt = tmpTable.Clone
        End If
        sonContainerDt.Columns.Add("FaContainerID")

        '子表也增加
        If sonContainerDt.Rows.Count > 0 Then
            sonContainerDt.Columns.Add("实发数/顺序号")
            sonContainerDt.Columns.Add("熔炉号子")
            sonContainerDt.Columns.Add("熔炉号母")
            sonContainerDt.Columns.Add("检验编号")
            sonContainerDt.Columns.Add("代料牌号")
            sonContainerDt.Columns.Add("代料规格")
            sonContainerDt.Columns.Add("代料单号")
            sonContainerDt.Columns.Add("平行作业工序")
            sonContainerDt.Columns.Add("平行作业单号")
            sonContainerDt.Columns.Add("CloseContianer") 'add tianFW 2017/09/08
            sonContainerDt.Columns.Add("canMove")
            strContianerIDList = ""
            For Each row As DataRow In sonContainerDt.Rows
                strContianerIDList = strContianerIDList + "'" + row("containerid").ToString().Trim() + "',"
            Next
            If strContianerIDList.Trim() <> "" Then
                strContianerIDList = strContianerIDList.Substring(0, strContianerIDList.Length - 1)
            End If

            sql = "SELECT c.containername,t.attributevalue,t.userattributename,t.parentid FROM Userattribute t LEFT JOIN container c ON c.containerid=t.parentid WHERE t.parentid IN (" & strContianerIDList & ")"
            Dim sonDataPointTable As DataTable = DatabaseHelper.Query_ReturnDatTable(sql)

            If sonDataPointTable.Rows.Count > 0 Then
                For Each row As DataRow In sonContainerDt.Rows
                    AddMaterialInfo(sonDataPointTable, row)
                Next
            End If
            sonContainerDt.AcceptChanges()
        End If
        '
        'ds.Tables(0).Columns("BEGINSTEP"), ds.Tables(1).Columns("BEGINSTEP") mfgworkflowid add tianFW 2018年1月9日
        Dim ds As New DataSet
        ds.Tables.Add(tmpTable)
        ds.Tables(0).TableName = "sumContainer"
        ds.Tables.Add(sonContainerDt)
        ds.Tables(1).TableName = "sonContainer"
        ds.Relations.Add("SumContainer",
        New DataColumn() {ds.Tables(0).Columns("PRODUCTID"), ds.Tables(0).Columns("TOGETHERFLAG"), ds.Tables(0).Columns("subworkflowid"),
                          ds.Tables(0).Columns("subFactoryID"), ds.Tables(0).Columns("CONTAINERTYPE"), ds.Tables(0).Columns("WORKFLOWSTEPID"), ds.Tables(0).Columns("MFGORDERNAME"), ds.Tables(0).Columns("mfgworkflowid")},
        New DataColumn() {ds.Tables(1).Columns("PRODUCTID"), ds.Tables(1).Columns("TOGETHERFLAG"), ds.Tables(1).Columns("subworkflowid"),
                          ds.Tables(1).Columns("subFactoryID"), ds.Tables(1).Columns("CONTAINERTYPE"), ds.Tables(1).Columns("WORKFLOWSTEPID"), ds.Tables(1).Columns("MFGORDERNAME"), ds.Tables(1).Columns("mfgworkflowid")})

        result.DBDataSet = ds

        Return result
    End Function

    ''' <summary>
    ''' 获取领料、工艺确认等开卡后的批次
    ''' add:Wangjh 20180822
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <param name="PageIndex"></param>
    ''' <param name="PageSize"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMainMaterialData(ByVal dto As MaterialParamterDto, ByVal PageIndex As Integer, ByVal PageSize As Integer) As uMESProcessResult Implements uLMSContainerMaterialIBusiness.GetMainMaterialData
        Dim strContainerName As String = ""
        If dto.strContainerName.Trim() <> "" Then
            strContainerName = dto.strContainerName.Trim()
        End If

        Dim roleIDs As String = ""
        Dim roleSql As String = "SELECT er.roleid  FROM employeerole er LEFT JOIN employee  e ON e.employeeid=er.employeeid WHERE LOWER(e.employeename)='" + dto.strEmployeeName.ToLower + "'"
        Dim roleDt = DatabaseHelper.Query_ReturnDatTable(roleSql)
        For Each row As DataRow In roleDt.rows
            roleIDs += "'" + row("roleid").ToString + "',"
        Next
        If Not String.IsNullOrWhiteSpace(roleIDs) Then
            roleIDs = roleIDs.Substring(0, roleIDs.Length - 1)
        End If

        Dim strSelect As String = <string>SELECT pt.producttypename,cci.containertype,cci.TogetherFlag,c.mfgworkflowid,cci.productname,cci.productrevision,cci.productid,cci.productdescription description,f.factoryname,cci.specname,cci.specrevision,ws.sequence, cci.workflowstepname,ws.islaststep,cci.specid,cci.workflowstepid,cl.containerlevelname,
cci.qty,cci.isselfmfg,c.materialrequirementdate,c.plannedstartdate, s.roleid,ut3.attributevalue as CheckTechnique,'' AS BeginStep,cci.mfgordername, wb2.WORKFLOWNAME ||':'|| w2.WORKFLOWREVISION  WORKFLOWINFO,
DECODE(Cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,(cci.productname||'/'||cci.containerno) ProductBatchHidden,cci.workflowroute,cci.workflowname||':'||cci.workflowrevision curworkflowinfo
                                  </string>
        Dim leftJoin As String = <string>LEFT JOIN container c ON c.containerid=cci.containerid
        LEFT JOIN containerlevel cl ON cl.containerlevelid=c.levelid
        LEFT JOIN spec s ON s.specid=cci.specid
        LEFT JOIN workflowstep ws ON ws.workflowstepid=cci.workflowstepid
        LEFT JOIN workflow w ON w.workflowid=cci.workflowid
        left join workflow w2 on w2.workflowid=c.mfgworkflowid
        left join workflowbase wb2 on wb2.workflowbaseid=w2.workflowbaseid
        LEFT JOIN cdodefinition cdo ON cdo.cdodefid=w.cdotypeid
        LEFT JOIN factory f ON f.factoryid=cci.originalfactoryid
        LEFT JOIN userattribute ut2 ON ut2.parentid=cci.containerid AND ut2.userattributename='BeginStep' 
        LEFT JOIN userattribute ut3 ON ut3.parentid=cci.containerid AND ut3.userattributename='CheckTechnique' 
        LEFT JOIN product p ON p.productid=cci.productid
        LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid</string>.Value
        Dim sql As StringBuilder = New StringBuilder()
        sql.AppendLine(strSelect)
        sql.AppendLine(" ,cci.containername,cci.containerid,c.factorystartdate,cci.QTY wipqty,c.factorystartqty sumfactorystartqty,w2.usequota") '增加 [w2.usequota] zhunan 2022.6.23
        sql.AppendLine(" FROM containercurrentinfo cci ")
        sql.AppendLine(leftJoin)      
        sql.AppendLine(" WHERE cci.CONTAINERTYPE<>7")     
        Dim strCondition As String = "  and c.status = 1 AND c.parentcontainerid IS NULL " & vbCrLf & _
                                     "AND cci.workflowstepname<>'完工入库' and cdo.cdoname='Workflow' "
        strCondition += String.Format("  and (CASE WHEN ws.workflowstepname='工艺确认' THEN cci.techniquedate ELSE NULL END) IS NULL  AND s.roleid IN ({0})", roleIDs)
        If Not String.IsNullOrWhiteSpace(dto.StrMaterialStep.Trim) Then
            If dto.IsMaterialStep Then
                strCondition += " AND cci.WORKFLOWSTEPNAME='" + dto.StrMaterialStep + "'"
            Else
                strCondition += " AND cci.WORKFLOWSTEPNAME<>'" + dto.StrMaterialStep + "'"
            End If
        End If

        If dto.IsEmp = True Then
             strCondition += " AND ut3.attributevalue= '" & dto.strEmployeeName & "' "
        End If

        If dto.WorkCenterID IsNot Nothing Then 'add tianFW 2018年1月9日 计划员属于工区
            strCondition += " AND C.WORKCENTERID= '" & dto.WorkCenterID & "' "
        End If


        If dto.strContainerName.Trim() <> "" Then
            strCondition += String.Format(" AND LOWER(cci.containername) like LOWER('%{0}%') ", strContainerName)
        End If

        ''产品图号
        If dto.ProductName.Trim <> "" Then
            strCondition += String.Format(" AND LOWER(cci.productname) like LOWER('%{0}%') ", dto.ProductName.Trim)
        End If

        ''车间，如果有“车间”两个字则过滤，否则不过滤
        Dim strFactoryName As String = dto.FactoryName
        If strFactoryName <> "" Then  'update:Wangjh 2017-6-28 去除 And strFactoryName.Contains("车间") 
             strCondition += "AND f.factoryname = '" & dto.FactoryName & "'" & vbCrLf
        End If

        If dto.strStartDate.Trim() <> "" Then
            strCondition += "AND c.plannedstartdate >= TO_DATE('" & dto.strStartDate & " 00:00:00','yyyy-mm-dd hh24:mi:ss')" & vbCrLf
        End If

        If dto.strEndDate.Trim() <> "" Then
            strCondition += "AND c.plannedstartdate <= TO_DATE('" & dto.strEndDate & " 23:59:59','yyyy-mm-dd hh24:mi:ss')" & vbCrLf
        End If
        If Not String.IsNullOrWhiteSpace(dto.MfgOrderName) Then
            strCondition += "AND cci.mfgordername ='" & dto.MfgOrderName & "'" & vbCrLf
        End If
        sql.AppendLine(strCondition)
        sql.AppendLine(" UNION")
        sql.AppendLine(strSelect)
        sql.AppendLine(" ,null containername,null containerid,null factorystartdate,SUM(c.qty) wipqty,SUM(c.factorystartqty) sumfactorystartqty,w2.usequota") '增加 [w2.usequota] zhunan 2022.6.23
        sql.AppendLine(" FROM containercurrentinfo cci ")
        sql.AppendLine(leftJoin)
        sql.AppendLine(" WHERE cci.CONTAINERTYPE=7")
        sql.AppendLine(strCondition)
        '增加 w2.usequota zhunan 2022.6.23
        sql.AppendLine(" GROUP BY pt.producttypename,cci.containertype,cci.TogetherFlag,c.mfgworkflowid,cci.productname,wb2.WORKFLOWNAME,w2.WORKFLOWREVISION,cci.productrevision,cCi.productid,cci.productdescription,f.factoryname, cci.specname,cci.specrevision,ws.sequence, cci.workflowstepname,ws.islaststep,cci.specid,cci.workflowstepid,cL.containerlevelname,cci.qty,c.factorystartqty,cci.isselfmfg,c.materialrequirementdate,c.plannedstartdate, s.roleid, ut3.Attributevalue,cci.mfgordername,cci.WORKFLOWNAME , cci.WORKFLOWREVISION,CCI.WORKFLOWROUTE,cci.containerno,w2.usequota")


        'sql += "ORDER BY c.togetherflag" & vbCrLf

        Dim para As uMESPagingDataDTO = New uMESPagingDataDTO()
        para.CurrentPageIndex = PageIndex
        para.PageSize = PageSize
        para.strSQL = sql.ToString
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(para)


        Dim sonSelect As String = " SELECT distinct cci.containername,cci.containerid,cci.productid,c.mfgworkflowid,cci.containertype,cci.workflowstepid,cci.togetherflag,c.factorystartdate,cci.isselfmfg,ut2.attributevalue BeginStep,cci.mfgordername"
        Dim sonSql As String = sonSelect & <string> FROM CONTAINERCURRENTINFO CCI LEFT JOIN container c ON c.containerid=cci.containerid
        LEFT JOIN workflow w ON w.workflowid=cci.workflowid
        LEFT JOIN cdodefinition cdo ON cdo.cdodefid=w.cdotypeid
        LEFT JOIN userattribute ut2 ON ut2.parentid=c.containerid AND ut2.userattributename='BeginStep'   
        LEFT JOIN userattribute ut ON ut.parentid=c.containerid AND ut.userattributename='CloseContianer'  
        </string>.Value

        Dim sonCondition As String = " where cdo.cdoname='Workflow' AND C.STATUS=1"
        sonCondition += " and (CASE WHEN cci.workflowstepname='工艺确认' THEN cci.techniquedate ELSE NULL END) IS NULL "
        If dto.WorkCenterID IsNot Nothing Then 'add tianFW 2018年1月9日 计划员属于工区
            sonCondition += " AND C.WORKCENTERID= '" & dto.WorkCenterID & "' "
        End If

        Dim sonDt As DataTable = New DataTable
        sonDt.Columns.Add("ChildContainerSql")
        sonDt.Columns("ChildContainerSql").DefaultValue = sonSql + sonCondition
        sonDt.Rows.Add()
        sonDt.AcceptChanges()

        result.DBTable.Merge(sonDt)
        result.DBTable.AcceptChanges()
        Dim temDt As DataTable = result.DBTable.Copy
        temDt.DefaultView.RowFilter = " ProductBatch is not null"
        result.DBTable = temDt.DefaultView.ToTable

        Return result
    End Function

    ''' <summary>
    ''' 查询批次详细领料信息
    ''' add:Wangjh 20180822
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMaterialContainerNoInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSContainerMaterialIBusiness.GetMaterialContainerNoInfo
        para("ChildContainerSql") += String.Format(" and((CCI.CONTAINERTYPE=7 AND CCI.TOGETHERFLAG='{0}' AND CCI.PRODUCTID='{1}' AND CCI.WORKFLOWSTEPID='{2}' AND CCI.MFGORDERNAME='{3}') OR (CCI.CONTAINERTYPE<>7 AND CCI.CONTAINERNAME='{4}') )",
                                                   para("TogetherFlag"), para("ProductID"), para("WorkflowStepID"), para("MfgOrderName"), para("ProductBatchHidden"))
        para("ChildContainerSql") += " ORDER BY CCI.CONTAINERNAME"
        Dim sonContainerDt As DataTable = DatabaseHelper.Query_ReturnDatTable(para("ChildContainerSql"))
        If sonContainerDt.Rows.Count > 0 Then
            sonContainerDt.Columns.Add("实发数/顺序号")
            sonContainerDt.Columns.Add("熔炉号子")
            sonContainerDt.Columns.Add("熔炉号母")
            sonContainerDt.Columns.Add("检验编号")
            sonContainerDt.Columns.Add("代料牌号")
            sonContainerDt.Columns.Add("代料规格")
            sonContainerDt.Columns.Add("代料单号")
            sonContainerDt.Columns.Add("平行作业工序")
            sonContainerDt.Columns.Add("平行作业单号")
            sonContainerDt.Columns.Add("CloseContianer") 'add tianFW 2017/09/08
            sonContainerDt.Columns.Add("canMove")
            sonContainerDt.Columns.Add("OrgContainerName") '补卡出来的卡此项是有值的
            Dim strContianerIDList As String = ""
            For Each row As DataRow In sonContainerDt.Rows
                strContianerIDList = strContianerIDList + "'" + row("containerid").ToString().Trim() + "',"
            Next
            If strContianerIDList.Trim() <> "" Then
                strContianerIDList = strContianerIDList.Substring(0, strContianerIDList.Length - 1)
            End If

            Dim Sql As String = "SELECT c.containername,t.attributevalue,t.userattributename,t.parentid FROM Userattribute t LEFT JOIN container c ON c.containerid=t.parentid WHERE t.parentid IN (" & strContianerIDList & ")"
            Dim sonDataPointTable As DataTable = DatabaseHelper.Query_ReturnDatTable(Sql)

            If sonDataPointTable.Rows.Count > 0 Then
                For Each row As DataRow In sonContainerDt.Rows
                    AddMaterialInfo(sonDataPointTable, row)
                Next
            End If
            sonContainerDt.AcceptChanges()
        End If
        '
        Return sonContainerDt

    End Function


    ''' <summary>
    ''' 获取零件明细
    ''' </summary>
    ''' <param name="productID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProductDetail(ByVal productID As String) As DataTable Implements uLMSContainerMaterialIBusiness.GetProductDetail
        Dim sql As String =
            String.Format("select t.materialsize,t.materialspecification,t.materialstatus,t.materialtrademark," +
                          "t.technicalconditions,m.materialtypename,t.MaterialName from product t " +
                          "left join materialtype m on m.materialtypeid=t.materialtypeid " +
                          "where t.productid='{0}'", productID)
        GetProductDetail = DatabaseHelper.Query_ReturnDatTable(sql)
    End Function
    ''' <summary>
    ''' 根据workflowid获取第一工序
    ''' </summary>
    ''' <param name="workflowid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFirstWorkflowStep(ByVal workflowid As String) As DataTable Implements uLMSContainerMaterialIBusiness.GetFirstWorkflowStep
        Dim sql As String =
    String.Format("select t.workflowstepid,t.workflowstepname,w.workflowrevision,wb.workflowname from workflowstep t " +
                  "left join workflow w on w.workflowid=t.workflowid " +
                  "left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid " +
                  "where t.sequence = 1 And t.workflowid ='{0}' ", workflowid)

        GetFirstWorkflowStep = DatabaseHelper.Query_ReturnDatTable(sql)

    End Function
    ''' <summary>
    ''' 根据当前stepID获取前一工序
    ''' </summary>
    ''' <param name="stepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetPreWorkflowStep(ByVal stepID As String) As DataTable Implements uLMSContainerMaterialIBusiness.GetPreWorkflowStep
        Dim sql As String =
    String.Format("select distinct p.fromstepid,p.tostepid,wfs.islaststep,s.specid||'|'||s.specrevision spec, sb.specname,wfs.workflowstepname,p.pathname,wf.workflowid,wfb.workflowname,wf.workflowrevision from path p " +
                  "left join workflowstep wfs on wfs.workflowstepid = p.fromstepid  " +
                  "left join workflow wf on wf.workflowid = wfs.workflowid  " +
                  "left join workflowbase wfb on wfb.workflowbaseid = wf.workflowbaseid " +
                  "left join spec s on (s.specid=wfs.specid and  wfs.specbaseid='0000000000000000') " +
                  "or ( wfs.specid='0000000000000000' and  s.specbaseid=wfs.specbaseid) " +
                  "left join specbase sb on sb.specbaseid = s.specbaseid " +
                  "where p.tostepid='{0}'", stepID)

        GetPreWorkflowStep = DatabaseHelper.Query_ReturnDatTable(sql)
    End Function
    ''' <summary>
    ''' 获取跟踪卡信息
    ''' </summary>
    ''' <param name="strContainerName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerInfo(ByVal strContainerName As String) As uMESProcessResult

        Dim strSql As String = "SELECT c.containername,c.togetherflag,pb.productname,cu.specid,cu.workflowstepid FROM container c " & vbCrLf & _
                               "LEFT JOIN product p ON p.productid=c.productid " & vbCrLf & _
                               "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                               "LEFT JOIN currentstatus cu ON cu.currentstatusid=c.currentstatusid " & vbCrLf & _
                               "WHERE c.containername='" & strContainerName & "'"

        Dim result As New uMESProcessResult
        Try
            Dim DT As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
    ''' <summary>
    ''' 添加实发数等信息
    ''' </summary>
    ''' <param name="dataPointTable"></param>
    ''' <param name="row"></param>
    ''' <remarks></remarks>
    Sub AddMaterialInfo(ByVal dataPointTable As DataTable, ByRef row As DataRow)
        Dim strFilter As String = String.Format("parentid='{0}' and userattributename='实发数/顺序号' ", row("containerid").ToString().Trim())
        Dim tmpRows As DataRow() = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("实发数/顺序号") = tmpRows(0)("attributevalue")
            End If
        End If
        strFilter = String.Format("parentid='{0}' and userattributename='熔炉号子' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("熔炉号子") = tmpRows(0)("attributevalue")
            End If
        End If
        strFilter = String.Format("parentid='{0}' and userattributename='熔炉号母' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("熔炉号母") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='检验编号' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("检验编号") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='代料牌号' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("代料牌号") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='代料规格' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("代料规格") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='代料单号' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("代料单号") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='平行作业工序' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("平行作业工序") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='平行作业单号' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("平行作业单号") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='CloseContianer' ", row("containerid").ToString().Trim()) 'add tianFW 2017/09/08
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("CloseContianer") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='原批次号' ", row("containerid").ToString().Trim()) 'add tianFW 2017/09/08
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("OrgContainerName") = tmpRows(0)("attributevalue")
            End If
        End If
    End Sub

    ''' <summary>
    ''' 查询子序号的相关领料信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetChildMaterialInfo(ByVal containerID As String) Implements uLMSContainerMaterialIBusiness.GetChildMaterialInfo
        Dim strSql As String = <string>
                                  SELECT C.CONTAINERNAME, (substr(c.batchcomments,0,INSTR(c.batchcomments,'实发数：')-1)) AS 熔炉号,(substr(c.batchcomments,INSTR(c.batchcomments,'实发数：')+4)) 实发数或顺序号   FROM container c 
                                  WHERE c.status=1 
                              </string>
        strSql += String.Format(" and c.Parentcontainerid='{0}'", containerID)
        GetChildMaterialInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 子工艺确认
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateSubWorkFlowCheckInfo(ByVal ID As String) Implements uLMSContainerMaterialIBusiness.UpdateSubWorkFlowCheckInfo
        Dim updateSql As String = <stirng>
                                      UPDATE SubWorkflowCheckInfo swc SET swc.checkresult=1,swc.checkdate=sysdate WHERE swc.subworkflowcheckinfoid IN (
                                    SELECT swc2.subworkflowcheckinfoid FROM subworkflowcheckinfo swc
                                    LEFT JOIN Workflow w ON w.workflowid=swc.subworkflowid
                                    LEFT JOIN Workflow w2 ON w2.process_no=w.process_no
                                    INNER JOIN subworkflowcheckinfo swc2 ON swc2.containerid=swc.containerid AND swc2.subworkflowid=w2.workflowid
                                    WHERE swc.subworkflowcheckinfoid=:subworkflowcheckinfoid 
                                    GROUP BY swc2.subworkflowcheckinfoid
                                    )
                                 </stirng>
        Dim updatePara(0) As OracleParameter
        updatePara(0) = DatabaseHelper.NewParameter(":subworkflowcheckinfoid", ID, OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(updateSql, updatePara)
        If i > 0 Then
            UpdateSubWorkFlowCheckInfo = True
        Else
            UpdateSubWorkFlowCheckInfo = False
        End If
    End Function
    ''' <summary>
    ''' 判断批次工艺是否校对完
    ''' </summary>
    ''' <param name="ContainerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function JudgeConfirmSubWorkFlow(ByVal ContainerID As String, ByVal IsSub As Boolean) As Boolean Implements uLMSContainerMaterialIBusiness.JudgeConfirmSubWorkFlow
        JudgeConfirmSubWorkFlow = False
        If IsSub = True Then '判断子工艺是否校对完
            Dim strSql As String = String.Format("SELECT swc.* FROM SubWorkflowCheckInfo swc " &
                                             "   LEFT JOIN Workflow w ON w.workflowid=swc.subworkflowid" &
                                             " LEFT JOIN Userattribute ut ON ut.parentid=w.factoryid AND ut.userattributename='子工艺校对'" &
                                              "  WHERE ut.attributevalue='是' and swc.containerid='{0}'", ContainerID)
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            For Each row As DataRow In dt.Rows
                If row("checkresult").ToString = "0" Then '未确认
                    Exit Function
                End If
            Next

        Else '判断主工艺是否校对完
            Dim strSql As String = String.Format("SELECT cci.techniquedate FROM containercurrentinfo cci WHERE cci.containerid='{0}'", ContainerID)
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            If dt.Rows.Count = 0 Then
                Exit Function
            End If
            If dt.Rows(0).IsNull("techniquedate") Then '未确认
                Exit Function
            End If
        End If

        JudgeConfirmSubWorkFlow = True
    End Function

    ''' <summary>
    ''' 更新批次的工艺信息 create tianFW 2018年1月9日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateContainerWorkflowInfo(ByVal para As Dictionary(Of String, Object)) As Boolean Implements uLMSContainerMaterialIBusiness.UpdateContainerWorkflowInfo
        UpdateContainerWorkflowInfo = False
        Dim SQLlist As New Dictionary(Of String, OracleParameter())()
        Dim strSQL As String
        For Each dr As DataRow In para("dt").Rows
            strSQL = String.Format("UPDATE CONTAINER C SET C.MFGWORKFLOWID='{0}' WHERE C.CONTAINERID='{1}'", para("WorkflowID"), dr("ContainerID"))
            '添加到哈希表
            SQLlist.Add(strSQL, Nothing)
            strSQL = "UPDATE USERATTRIBUTE UT SET "
            strSQL += String.Format(" UT.ATTRIBUTEVALUE='{0}' WHERE UT.USERATTRIBUTENAME='BeginStep' AND UT.PARENTID='{1}'", para("BeginStep"), dr("ContainerID"))
            '添加到哈希表
            SQLlist.Add(strSQL, Nothing)
        Next
        DatabaseHelper.ExecuteSqlTranByHash(SQLlist)
        UpdateContainerWorkflowInfo = True
    End Function

    ''' <summary>
    ''' 根据计划员获取该计划员开卡未校对的流水卡信息 create tianFW 2018年4月17日
    ''' 将单件单卡整合一条查询 modify by tianFW 2018年10月18日 (查询CONTAINERNO 删除CONTAINERNAME和C.CONTAINERID查询)
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerInfoNoProofread(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSContainerMaterialIBusiness.GetContainerInfoNoProofread
        Dim strsql As String =
            <string>
                SELECT CONTAINERNO,NVL(workflowroute,PRODUCTNAME) PRODUCTNAME,NVL(workflowroute,PRODUCTNAME)||'/'||containerno containername,PRODUCTREVISION,sum(QTY) QTY,CHECKTECHNIQUE,ORIGINALSTARTDATE,PRODUCTID FROM (
                SELECT Cc.CONTAINERNO,cc.workflowroute,PB.PRODUCTNAME,P.PRODUCTREVISION,C.QTY,EM.FULLNAME CHECKTECHNIQUE,
        to_char(C.ORIGINALSTARTDATE,'yyyy/MM/dd hh24:Mi') ORIGINALSTARTDATE,C.PRODUCTID
            FROM CONTAINER C
                LEFT JOIN containercurrentinfo cc ON cc.containerid = c.containerid          
            LEFT JOIN CURRENTSTATUS CU ON CU.CURRENTSTATUSID = C.CURRENTSTATUSID
              LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CU.WORKFLOWSTEPID
        LEFT JOIN USERATTRIBUTE UT ON UT.PARENTID = C.CONTAINERID AND UT.USERATTRIBUTENAME='CheckTechnique'
        LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEENAME = UT.ATTRIBUTEVALUE
        LEFT JOIN PRODUCT P ON P.PRODUCTID = C.PRODUCTID
              LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID
        LEFT JOIN WORKFLOW W ON W.WORKFLOWID = WS.WORKFLOWID
              LEFT JOIN CDODEFINITION CDO ON CDO.CDODEFID = W.CDOTYPEID
            </string>
        Dim sql As New StringBuilder
        sql.AppendLine(strsql)
        sql.AppendLine("WHERE C.STATUS = 1 AND C.CONTAINERNAME <> C.CONTAINERID AND C.PARENTCONTAINERID IS NULL")
        sql.AppendLine("AND WS.WORKFLOWSTEPNAME IS NOT NULL AND CDO.CDONAME = 'Workflow'")
        sql.AppendFormat("AND （C.STARTEMPLOYEEID = '{0}' OR EM.EMPLOYEEID = '{1}') AND WS.WORKFLOWSTEPNAME = '工艺确认')", para("EmployeeID"), para("EmployeeID"))
        sql.AppendLine("GROUP BY workflowroute,CONTAINERNO,PRODUCTNAME,PRODUCTREVISION,QTY,CHECKTECHNIQUE,ORIGINALSTARTDATE,PRODUCTID")
        Return DatabaseHelper.Query_ReturnDatTable(sql.ToString())
    End Function

    ''' <summary>
    ''' 获取到料情况 add zhunan 2018.4.20
    ''' </summary>
    ''' <param name="paraDic"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMaterialInfo(ByVal paraDic As System.Collections.Generic.Dictionary(Of String, String)) As System.Data.DataTable Implements uLMSContainerMaterialIBusiness.GetMaterialInfo
        Dim sb = New StringBuilder()
        sb.AppendLine("SELECT cci.togetherflag,cci.productname,cci.containername,cci.qty,c.originalstartdate startDate,cci.containertype,")
        sb.AppendLine("       CASE WHEN ut.attributevalue IS NULL AND cci.workflowstepname = '到料确认' THEN '未发料'")
        sb.AppendLine("       WHEN ut.attributevalue IS NOT NULL AND cci.workflowstepname = '到料确认' THEN '未到料'")
        sb.AppendLine("       WHEN cci.workflowstepname = '工人领料' THEN '已到料' END status ")
        sb.AppendLine("FROM containercurrentinfo cci")
        sb.AppendLine("LEFT JOIN container c ON cci.containerid = c.containerid")
        sb.AppendLine("LEFT JOIN userattribute ut ON cci.containerid=ut.parentid AND ut.userattributename='实发数/顺序号'")
        sb.AppendLine("WHERE cci.isselfmfg = 0 AND cci.workflowstepname <> '工艺确认' AND cci.isinstore = -1")

        If paraDic.ContainsKey("FactoryID") Then
            sb.AppendFormat("AND cci.originalfactoryid = '{0}'", paraDic("FactoryID"))
        End If

        Dim dtMateria As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())

        Dim dtContainer As DataTable = dtMateria.DefaultView.ToTable(True, "togetherflag", "productname", "containertype", "status")
        dtContainer.Columns.Add("qty", GetType(Int32)) '批次数量
        dtContainer.Columns.Add("startDate", GetType(DateTime)) '开卡时间

        For Each dr As DataRow In dtContainer.Rows
            Dim notSingleContainerFilter = String.Format("togetherflag = '{0}' and containertype <> 7", dr("togetherflag"))
            Dim SingleContainerFilter = String.Format("togetherflag = '{0}' and status = '{1}' and containertype = 7", dr("togetherflag"), dr("status"))

            Dim drNotSingleContainer = dtMateria.Select(notSingleContainerFilter)
            Dim drSingleContainer = dtMateria.Select(SingleContainerFilter)

            If drNotSingleContainer.Length > 0 Then
                dr("qty") = drNotSingleContainer(0)("qty")
                dr("startDate") = drNotSingleContainer(0)("startDate")
            End If


            If drSingleContainer.Length > 0 Then
                dr("qty") = dtMateria.Compute("Count(containername)", SingleContainerFilter)
                dr("startDate") = dtMateria.Select(SingleContainerFilter)(0)("startDate")
            End If
        Next

        Return dtContainer
    End Function
    ''' <summary>
    ''' 查询原工艺号
    ''' </summary>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProcess_No(ByVal workflowID As String) As DataTable Implements uLMSContainerMaterialIBusiness.GetProcess_No
        Dim strSql As String = String.Format("SELECT w.process_no FROM Workflow w WHERE w.workflowid='{0}'", workflowID)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取批次需要校对的子工艺，及批次是否需要子工艺校对
    ''' add:Wangjh 20180620
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNeedCheckSubWorkflow(ByVal containerID As String, ByRef subWorkflows As String) As Boolean Implements uLMSContainerMaterialIBusiness.GetNeedCheckSubWorkflow
        Dim strSql As String = <string>SELECT w.factoryid subfactoryid,swc.* FROM SubWorkflowCheckInfo swc LEFT JOIN workflow w ON w.workflowid=swc.subworkflowid</string>
        strSql += String.Format(" WHERE swc.containerid='{0}' AND swc.checkresult=0", containerID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        For Each row As DataRow In dt.Rows
            If m_CommonObj.GetFatoryAttributes(row("subfactoryid").ToString(), "子工艺校对") = "是" Then
                If String.IsNullOrWhiteSpace(subWorkflows) Then
                    subWorkflows = "'" + row("Subworkflowid").ToString + "'"
                Else
                    subWorkflows += ",'" + row("Subworkflowid").ToString + "'"
                End If
            End If
        Next
        If String.IsNullOrWhiteSpace(subWorkflows) Then '无需要校对的子工艺
            GetNeedCheckSubWorkflow = False
        Else '有需要校对的子工艺
            GetNeedCheckSubWorkflow = True
        End If

    End Function

    ''' <summary>
    ''' 查询父卡
    ''' add:Wangjh 20190103
    ''' </summary>
    ''' <param name="workflowroute"></param>
    ''' <param name="containerNum"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFaContainer(ByVal workflowroute As String, ByVal containerNum As String, ByVal sonContainerID As String) As DataTable Implements uLMSContainerMaterialIBusiness.GetFaContainer
        Dim strSql As String = <string>SELECT cci.workflowroute,cci.productname,cci.containerid,cci.containername,(case when cci.isinstore='0' or cci.workflowstepname='工人领料' or cci.isinstore='1' then 1 else 0 end) ISmaterial  FROM containercurrentinfo cci WHERE cci.containerstatus=1 AND cci.productname NOT LIKE '%子工艺%'</string>
        strSql += String.Format(" AND (cci.workflowroute='{0}' OR cci.productname='{0}') AND REPLACE(cci.containername,cci.productname,NULL)='{1}'", workflowroute, containerNum)

        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count = 0 Then 'add:Wangjh 20211111
            strSql = String.Format(<string> SELECT cci.workflowroute,cci.productname,cci.containerid,cci.containername,(case when cci.isinstore='0' or cci.workflowstepname='工人领料' or cci.isinstore='1' then 1 else 0 end) ISmaterial  FROM containercurrentinfo cci 
 left join containersubcontainer cc on cc.containerid=cci.containerid
 WHERE cci.containerstatus=1  and cc.subcontainerid='{0}'</string>.Value, sonContainerID)
            dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 继承父卡的领料信息
    ''' </summary>
    ''' <param name="faContainername"></param>
    ''' <param name="sonContainerID"></param>
    ''' <remarks></remarks>
    Public Sub inheritFaContainer(ByVal faContainername As String, ByVal sonContainerID As String) Implements uLMSContainerMaterialIBusiness.inheritFaContainer
        Dim strSql As String = <string>
                                 SELECT DISTINCT ut.userattributename,ut.attributevalue FROM Userattribute ut
                                LEFT JOIN container c ON c.containerid=ut.parentid
                                WHERE (ut.userattributename='实发数/顺序号' OR ut.userattributename='熔炉号子' OR ut.userattributename='熔炉号母' OR ut.userattributename='检验编号' OR ut.userattributename='代料牌号' OR ut.userattributename='代料规格' OR ut.userattributename='平行作业工序' OR ut.userattributename='代料单号' OR ut.userattributename='平行作业单号' ) 
                                 and c.containername=
                             </string>.Value + "'" + faContainername + "'"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        For Each row As DataRow In dt.Rows
            Dim sql = "UPDATE Userattribute ut SET ut.attributevalue='" + row("attributevalue").ToString + "' WHERE ut.userattributename='" + row("userattributename").ToString + "'"
            sql += " and ut.Parentid='" + sonContainerID + "'"
            DatabaseHelper.ExecuteSql(sql)
        Next

    End Sub

    ''' <summary>
    ''' 获取批次的物资退回记录
    ''' add:Wangjh 20190327
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWuziReturnContainerInfo(ByVal containerID As String) As DataTable Implements uLMSContainerMaterialIBusiness.GetWuziReturnContainerInfo
        Dim strSql As String = <string>SELECT e.fullname,substr( nv.notes,INSTR(nv.notes,':')+1) notes,nv.txnid,c.containername,ut.attributevalue FROM MOVEHISTORY nv 
                        LEFT JOIN employee e ON e.employeeid=nv.reportemployeeid
                        LEFT JOIN workflowstep ws ON ws.workflowstepid=nv.stepid
                        LEFT JOIN workflowstep ws2 ON ws2.workflowstepid=nv.tostepid
                        LEFT JOIN container c ON c.containerid=nv.historyid
                        LEFT JOIN Userattribute ut ON ut.parentid=c.containerid AND ut.userattributename='代料单号' AND nv.notes LIKE '%代料重审%'
                        WHERE ws.sequence=2 AND ws2.workflowstepname='工艺确认'  AND nv.changecount=1</string>.Value
        strSql += String.Format(" and nv.historyid='{0}' AND nv.notes LIKE '物资退回:%'", containerID)
        strSql += " ORDER BY nv.txnid DESC"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
End Class
